package dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import constants.Constants;
import domain.milk.SeasonalityMilk;
import domain.milk.WoodsCurves;

public class MilkDao {
	FileDao fileDao = new FileDao();

	String dbUrl = Constants.DB_URL;
	String dbUsername = Constants.DB_USERNAME;
	String dbPassword = Constants.DB_PASSWORD;
	
	public SeasonalityMilk getSeasonality(int fileNo){
		SeasonalityMilk result = new SeasonalityMilk();
		String query = "select Lact, Abs_Adjustment, Pct_Adjustment, Seasonality, Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec from dbo.DVIPV_MILK_SEASONALITY where file_no = "+fileNo;
		
		Connection sqlConnection = null;
		try {
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
		} catch (ClassNotFoundException e1) {
			e1.printStackTrace();
		}
		
		Statement stmt = null;
		ResultSet  rs = null;
		try {
			sqlConnection = DriverManager.getConnection(dbUrl, dbUsername, dbPassword);
			stmt = sqlConnection.createStatement();
			rs = stmt.executeQuery(query);
			
			while(rs.next())
			{
				int lact = rs.getInt("Lact");
				result.getAbsAdjustment()[lact] = rs.getDouble("Abs_Adjustment");
				result.getPctAdjustment()[lact] = rs.getDouble("Pct_Adjustment");
				result.getOverallSeasonality()[lact] = rs.getDouble("Seasonality");
				
				for (int i=1;i<=12;i++){
					result.getMonthlySeasonality()[lact][i] = rs.getDouble(Constants.MONTHS[i]);
				}
			}
			rs.close();
			stmt.close();
			sqlConnection.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		
		return result;
	}
	
	public WoodsCurves getWoodsCurves(int fileNo){
		WoodsCurves result = new WoodsCurves();
		String query = "select Lact1a, Lact1b, Lact1c, Lact2a, Lact2b, Lact2c, Lact3a, Lact3b, Lact3c  from dbo.DVIPV_MILK_WOOD_CURVE where file_no = "+fileNo;
		
		Connection sqlConnection = null;
		try {
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
		} catch (ClassNotFoundException e1) {
			e1.printStackTrace();
		}
		
		Statement stmt = null;
		ResultSet  rs = null;
		try {
			sqlConnection = DriverManager.getConnection(dbUrl, dbUsername, dbPassword);
			stmt = sqlConnection.createStatement();
			rs = stmt.executeQuery(query);
			
			while(rs.next())
			{
				for(int lact=1; lact<=3; lact++){
					result.getLactA()[lact] = rs.getDouble("Lact"+lact+"a");
					result.getLactB()[lact] = rs.getDouble("Lact"+lact+"b");
					result.getLactC()[lact] = rs.getDouble("Lact"+lact+"c");
						
				}
			}
			rs.close();
			stmt.close();
			sqlConnection.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return result;
	}
	
	public double[] getMilkYieldLact(){
		double[] result = new double[Constants.LACT+1];
		String query = "select Lact4, Lact5, Lact6, Lact7, Lact8, Lact9, Lact10, Lact11, Lact12 from dbo.DVIP_GLB_MILK_YIELD_LACT";
		
		Connection sqlConnection = null;
		try {
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
		} catch (ClassNotFoundException e1) {
			e1.printStackTrace();
		}
		
		Statement stmt = null;
		ResultSet  rs = null;
		try {
			sqlConnection = DriverManager.getConnection(dbUrl, dbUsername, dbPassword);
			stmt = sqlConnection.createStatement();
			rs = stmt.executeQuery(query);
			
			while(rs.next())
			{
				for(int i=4;i<=Constants.LACT;i++){
					result[i] = rs.getDouble("Lact"+i);
				}
			}
			rs.close();
			stmt.close();
			sqlConnection.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return result;
	}
	
	public double[] getMilkYieldPreg(){
		double[] result = new double[Constants.MPREG+1];
		String query = "select Preg_Month1, Preg_Month2, Preg_Month3, Preg_Month4, Preg_Month5, Preg_Month6, Preg_Month7, Preg_Month8, Preg_Month9 from dbo.DVIP_GLB_MILK_YIELD_PREG";
		
		Connection sqlConnection = null;
		try {
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
		} catch (ClassNotFoundException e1) {
			e1.printStackTrace();
		}
		
		Statement stmt = null;
		ResultSet  rs = null;
		try {
			sqlConnection = DriverManager.getConnection(dbUrl, dbUsername, dbPassword);
			stmt = sqlConnection.createStatement();
			rs = stmt.executeQuery(query);
			
			while(rs.next())
			{
				for(int i=1;i<=Constants.MPREG;i++){
					result[i] = rs.getDouble("Preg_Month"+i);
				}
			}
			rs.close();
			stmt.close();
			sqlConnection.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return result;
	}
	
	public boolean updateSeasonality(int fileNo, SeasonalityMilk obj){
		boolean result = true;
		String query = "update dbo.DVIP_IN_MILK_SEASONALITY set Abs_Adjustment = ?, Pct_Adjustment = ?, Seasonality = ?, Jan = ?, Feb = ?, Mar = ?, Apr = ?, May = ?, Jun = ?, Jul = ?, Aug = ?, Sep = ?, Oct = ?, Nov = ?, Dec = ? where file_no = ? and lact = ?";
		
		Connection sqlConnection = null;
		try {
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
		} catch (ClassNotFoundException e1) {
			e1.printStackTrace();
		}
		
		PreparedStatement pStmt = null;
		try {
			sqlConnection = DriverManager.getConnection(dbUrl, dbUsername, dbPassword);
			pStmt = sqlConnection.prepareStatement(query);
			
			for(int lact=1; lact<=3; lact++){
				int colIndex = 1;
				int month = 1;
				pStmt.setDouble(colIndex++, obj.getAbsAdjustment()[lact]);
				pStmt.setDouble(colIndex++, obj.getPctAdjustment()[lact]);
				pStmt.setDouble(colIndex++, obj.getOverallSeasonality()[lact]);
				
				for(int sea=1; sea<=12; sea++){
					pStmt.setDouble(colIndex++, obj.getMonthlySeasonality()[lact][month++]);	
				}
				
				pStmt.setInt(colIndex++, fileNo);
				pStmt.setInt(colIndex++, lact);
				
				pStmt.addBatch();
			}
			
			
			int[] res = pStmt.executeBatch();
			
			for(int i=0; i<res.length; i++){
				if(res[i]<=0){
					result = false;
				}
			}
			
			pStmt.close();
			sqlConnection.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}
	
	public boolean updateWoodsCurves(int fileNo, WoodsCurves obj){
		boolean result = false;
		String query = "update dbo.DVIP_IN_MILK_WOOD_CURVE set Lact1a = ?, Lact1b = ?, Lact1c = ?, Lact2a = ?, Lact2b = ?, Lact2c = ?, Lact3a = ?, Lact3b = ?, Lact3c = ? where file_no = ?";
		
		Connection sqlConnection = null;
		try {
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
		} catch (ClassNotFoundException e1) {
			e1.printStackTrace();
		}
		
		PreparedStatement pStmt = null;
		try {
			sqlConnection = DriverManager.getConnection(dbUrl, dbUsername, dbPassword);
			pStmt = sqlConnection.prepareStatement(query);
			int colIndex = 1;
			for(int lact=1; lact<=3; lact++){
				pStmt.setDouble(colIndex++, obj.getLactA()[lact]);
				pStmt.setDouble(colIndex++, obj.getLactB()[lact]);
				pStmt.setDouble(colIndex++, obj.getLactC()[lact]);
			}
			pStmt.setInt(colIndex++, fileNo);
			
			int res = pStmt.executeUpdate();
			if(res>0){
				result = true;
			}
			pStmt.close();
			sqlConnection.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}
	
	public boolean updateMilkYieldLact(int fileNo, double[] obj){
		boolean result = false;
		String query = "update dbo.DVIP_IN_MILK_YIELD_LACT set Lact4 = ?, Lact5 = ?, Lact6 = ?, Lact7 = ?, Lact8 = ?, Lact9 = ?, Lact10 = ?, Lact11 = ?, Lact12 = ? where file_no = ?";
		
		Connection sqlConnection = null;
		try {
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
		} catch (ClassNotFoundException e1) {
			e1.printStackTrace();
		}
		
		PreparedStatement pStmt = null;
		try {
			sqlConnection = DriverManager.getConnection(dbUrl, dbUsername, dbPassword);
			pStmt = sqlConnection.prepareStatement(query);
			int colIndex = 1;
			int lact = 4;
			pStmt.setDouble(colIndex++, obj[lact++]);
			pStmt.setDouble(colIndex++, obj[lact++]);
			pStmt.setDouble(colIndex++, obj[lact++]);
			
			pStmt.setDouble(colIndex++, obj[lact++]);
			pStmt.setDouble(colIndex++, obj[lact++]);
			pStmt.setDouble(colIndex++, obj[lact++]);
			
			pStmt.setDouble(colIndex++, obj[lact++]);
			pStmt.setDouble(colIndex++, obj[lact++]);
			pStmt.setDouble(colIndex++, obj[lact++]);
			
			pStmt.setInt(colIndex++, fileNo);
			
			int res = pStmt.executeUpdate();
			if(res>0){
				result = true;
			}
			pStmt.close();
			sqlConnection.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}
	
	public boolean updateMilkYieldPreg(int fileNo, double[] obj){
		boolean result = false;
		String query = "update dbo.DVIP_IN_MILK_YIELD_PREG set Preg_Month1 = ?, Preg_Month2 = ?, Preg_Month3 = ?, Preg_Month4 = ?, Preg_Month5 = ?, Preg_Month6 = ?, Preg_Month7 = ?, Preg_Month8 = ?, Preg_Month9 = ? where file_no = ?";
		
		Connection sqlConnection = null;
		try {
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
		} catch (ClassNotFoundException e1) {
			e1.printStackTrace();
		}
		
		PreparedStatement pStmt = null;
		try {
			sqlConnection = DriverManager.getConnection(dbUrl, dbUsername, dbPassword);
			pStmt = sqlConnection.prepareStatement(query);
			int colIndex = 1;
			int month = 1;
			pStmt.setDouble(colIndex++, obj[month++]);
			pStmt.setDouble(colIndex++, obj[month++]);
			pStmt.setDouble(colIndex++, obj[month++]);
			
			pStmt.setDouble(colIndex++, obj[month++]);
			pStmt.setDouble(colIndex++, obj[month++]);
			pStmt.setDouble(colIndex++, obj[month++]);
			
			pStmt.setDouble(colIndex++, obj[month++]);
			pStmt.setDouble(colIndex++, obj[month++]);
			pStmt.setDouble(colIndex++, obj[month++]);
			
			pStmt.setInt(colIndex++, fileNo);
			
			int res = pStmt.executeUpdate();
			if(res>0){
				result = true;
			}
			pStmt.close();
			sqlConnection.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}
	
}
